package com.ningxia.water.mapper;

import com.ningxia.water.entity.ZtDistrictInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.entity.ZtTownInfo;
import com.ningxia.water.vo.out.ReportVo;
import com.ningxia.water.vo.out.ZonePidVo;
import com.ningxia.water.vo.out.ZoneVo;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author hz
 * @since 2021-05-14
 */
public interface ZtDistrictInfoMapper extends BaseMapper<ZtDistrictInfo> {

    /**
     * 获取区县信息
     *
     * @return
     */
    @Select("SELECT\n" +
            "\tci.city_id AS pid,\n" +
            "\tdi.district_id as zoneId,\n" +
            "\tci.city_name AS city,\n" +
            "\tdi.district_name AS district\t\n" +
            "FROM\n" +
            "\tzt_district_info di\n" +
            "\tLEFT JOIN zt_city_info ci ON di.city_id = ci.city_id")
    List<ZoneVo> getDistrictInfos();

    /**
     * 查看此区县ID下是否有乡镇存在
     *
     * @param districtId
     * @return
     */
    @Select("SELECT count(1) as num FROM zt_town_info t WHERE t.district_id = #{districtId}")
    Integer findByDistrictId(Integer districtId);

    /**
     * 查看此市下是否有区县存在
     *
     * @param cityId
     * @return
     */
    @Select("SELECT count(1) as num FROM zt_district_info d WHERE d.city_id = #{cityId}")
    Integer findByCityId(Integer cityId);

    /**
     * 获取该区县下的所有乡镇
     *
     * @param id
     * @return
     */
    @Select("select town_id from zt_town_info where district_id = #{id}")
    List<Integer> getAllTownIds(Integer id);

    /**
     * 获取该区县下的所有乡镇
     *
     * @param districtId
     * @return
     */
    @Select("select * from zt_town_info where district_id = #{districtId}")
    List<ZtTownInfo> getAllTown(Integer districtId);


    /**
     * 返回区县年报
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "di.district_id as id, " +
            "di.district_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM zt_district_info di \n" +
            "LEFT JOIN zt_town_info ti ON di.district_id = ti.district_id\n" +
            "LEFT JOIN zt_village_info vi on ti.town_id = vi.town_id\n" +
            "LEFT JOIN ts_well_info wi ON vi.village_id = wi.zone_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time BETWEEN concat(#{currentTime}, '-01') and concat(#{currentTime}, '-12')\n" +
            "GROUP BY di.district_id")
    List<ReportVo> getDistrictReportByYear(String currentTime);

    /**
     * 获取区县月报
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "di.district_id as id, " +
            "di.district_name as name, " +
            "sum(md.month_data)/10000 as volume\n" +
            "FROM zt_district_info di \n" +
            "LEFT JOIN zt_town_info ti ON di.district_id = ti.district_id\n" +
            "LEFT JOIN zt_village_info vi on ti.town_id = vi.town_id\n" +
            "LEFT JOIN ts_well_info wi ON vi.village_id = wi.zone_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time = #{currentTime}\n" +
            "GROUP BY di.district_id")
    List<ReportVo> getDistrictReportByMonth(String currentTime);

    /**
     * 获取区县日报
     * @param currentTime   当前查询时间
     * @return
     */
    @Select("SELECT " +
            "di.district_id as id, " +
            "di.district_name as name, " +
            "sum(dd.day_data)/10000 as volume\n" +
            "FROM zt_district_info di \n" +
            "LEFT JOIN zt_town_info ti ON di.district_id = ti.district_id\n" +
            "LEFT JOIN zt_village_info vi on ti.town_id = vi.town_id\n" +
            "LEFT JOIN ts_well_info wi ON vi.village_id = wi.zone_id and wi.zone_type = 2\n" +
            "LEFT JOIN ts_day_data dd ON wi.well_id = dd.well_id and dd.time = #{currentTime}\n" +
            "GROUP BY di.district_id")
    List<ReportVo> getDistrictReportByDay(String currentTime);

    /**
     * 获取区县及其对应城市id
     * @return
     */
    @Select("SELECT\n" +
            "\tci.city_id AS pid,\n" +
            "\tdi.district_id AS id,\n" +
            "\tdi.district_name AS name \n" +
            "FROM\n" +
            "\tzt_district_info di\n" +
            "\tLEFT JOIN zt_city_info ci ON di.city_id = ci.city_id")
    List<ZonePidVo> getDistrictRelationship();

}
